﻿Imports System
Imports System.Data
Imports System.Data.Common
Imports System.Collections.Generic
Imports MySql.Data.MySqlClient

''' <summary>
''' SQLite数据库访问类
''' </summary>
''' <remarks></remarks>
Public Class SqlHelper

    Private Shared _ConnectionString As String
    Private Shared lockObject As New Object

    ''' <summary>
    ''' 共享构造函数
    ''' </summary>
    ''' <remarks></remarks>
    Shared Sub New()
        ' _ConnectionString = String.Format("Data Source=localhost;port=3307;user id=root;password=tbcc;database=tbcc_lsc_db;Character Set=gbk;Connect Timeout=100")


        _ConnectionString = readConfig("MySqlConnection")
        '_ConnectionString1 = String.Format("Data Source={0}", Application.StartupPath & "\config11.dat")
    End Sub

    Public Shared Function readConfig(ByVal strkey As String) As String

        Dim strreturn As String
        Dim appreader As System.Configuration.AppSettingsReader = New System.Configuration.AppSettingsReader()
        Try
            strreturn = appreader.GetValue(strkey, Type.GetType("System.String")).ToString()
        Catch ex As Exception
            strreturn = ex.Message.ToString()
        Finally
            appreader = Nothing
        End Try
        Return strreturn
    End Function
    Public Shared Sub SetDatabaseFileName(ByVal file As String)
        _ConnectionString = file

        If file = "" Then
            _ConnectionString = "Data Source=localhost;user id=root;password=root;database=tbcc_lsc_db"
        End If
    End Sub

    Private Shared _cn As MySqlConnection = Nothing
    Public Shared Function GetDBConn() As String
        Return _ConnectionString
    End Function

    Private Shared Function GetConnection() As MySqlConnection
        Try

            If _cn Is Nothing Then
                _cn = New MySqlConnection(_ConnectionString)
            End If

            If _cn.State = ConnectionState.Closed Then
                _cn.Open()
            End If

            Return _cn

        Catch ex As Exception
            ' msg.ShowError("连接数据库失败！")
            Throw ex

        End Try

    End Function
    ''' <summary>
    ''' 执行选择操作
    ''' </summary>
    ''' <param name="cmdText"></param>
    ''' <param name="commandParameters"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function ExecuteDataset(ByVal cmdText As String, ByVal commandParameters As System.Data.Common.DbParameter()) As DataSet
        Dim ds As New DataSet
        Dim cn As MySqlConnection = Nothing
        If System.Threading.Monitor.TryEnter(lockObject, -1) Then

            Try
                cn = GetConnection()
                Dim cmd As New MySqlCommand()

                cmd.Connection = cn
                cmd.CommandType = CommandType.Text
                cmd.CommandText = cmdText
                cmd.CommandTimeout = 120
                If commandParameters IsNot Nothing Then
                    cmd.Parameters.AddRange(commandParameters)
                End If
                Dim da As New MySqlDataAdapter(cmd)
                '当禁用网卡后，第一次使用连接Sql数据库时异常
                Try

                    da.Fill(ds)
                Catch ex As System.Data.SqlClient.SqlException
                    Debug.WriteLine(ex.Message)
                End Try

                'da.Dispose()
            Catch ex As Exception
                'msg.log(ex.Message)
                Debug.WriteLine(ex.Message)
            Finally
                If cn.State = ConnectionState.Open Then
                    cn.Close()
                End If
                System.Threading.Monitor.Exit(lockObject)

            End Try

        End If


        Return ds
    End Function

    ''' <summary>
    ''' 执行更新操作
    ''' </summary>
    ''' <param name="cmdText"></param>
    ''' <param name="commandParameters"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function ExecuteNonQuery(ByVal cmdText As String, ByVal commandParameters As System.Data.Common.DbParameter()) As Integer

        Dim cmd As New MySqlCommand()

        
        If commandParameters IsNot Nothing Then
            cmd.Parameters.AddRange(commandParameters)
        End If

        Dim result As Integer = 0
        If System.Threading.Monitor.TryEnter(lockObject, -1) Then
            Dim cn As MySqlConnection = GetConnection()

            Try
                cmd.Connection = cn
                cmd.CommandType = CommandType.Text
                cmd.CommandText = cmdText
                cmd.CommandTimeout = 120
                If cn.State = ConnectionState.Closed Then
                    cn.Open()
                End If
                result = cmd.ExecuteNonQuery()
            Catch ex As Exception
                result = -1
                'msg.log(ex.Message)
                Debug.WriteLine(ex.Message)
            Finally
                If cn.State = ConnectionState.Open Then
                    cn.Close()
                End If
                System.Threading.Monitor.Exit(lockObject)
            End Try

        End If


        Return result

    End Function

    Public Shared Function ExecuteScalar(ByVal cmdText As String, ByVal commandParameters As System.Data.Common.DbParameter()) As Object
        Dim obj As Object = Nothing

        If System.Threading.Monitor.TryEnter(lockObject, -1) Then
            Dim cn As MySqlConnection = GetConnection()
            Dim cmd As New MySqlCommand()

            cmd.Connection = cn
            cmd.CommandType = CommandType.Text
            cmd.CommandText = cmdText
            cmd.CommandTimeout = 120
            If commandParameters IsNot Nothing Then
                cmd.Parameters.AddRange(commandParameters)
            End If
            Try
                If cn.State = ConnectionState.Closed Then
                    cn.Open()
                End If
                obj = cmd.ExecuteScalar()
            Catch ex As Exception
                obj = Nothing
                'msg.log(ex.Message)
                Debug.WriteLine(ex.Message)
            Finally
                If cn.State = ConnectionState.Open Then
                    cn.Close()
                End If
                System.Threading.Monitor.Exit(lockObject)
            End Try

        End If


        Return obj
    End Function

    Public Shared Function ExecuteTranstration(ByVal tic As TranstrationItem) As Boolean
        If System.Threading.Monitor.TryEnter(lockObject, -1) Then


            Dim cn As MySqlConnection = GetConnection()
            Dim cmd As New MySqlCommand()
            cmd.Connection = cn
            cmd.CommandTimeout = 120

            Dim t As MySqlTransaction = Nothing

            Try
                If cn.State = ConnectionState.Closed Then
                    cn.Open()
                End If

                '开始事务
                t = cn.BeginTransaction()
                cmd.Transaction = t
                For Each item As KeyValuePair(Of String, System.Data.Common.DbParameter()) In tic
                    cmd.CommandText = item.Key

                    cmd.Parameters.Clear()

                    If item.Value IsNot Nothing Then
                        cmd.Parameters.AddRange(item.Value)
                    End If

                    cmd.ExecuteNonQuery()
                Next

                '提交事务
                t.Commit()
                Return True

            Catch ex As Exception
                Try
                    If t IsNot Nothing Then
                        '回滚事务
                        t.Rollback()
                    End If
                Catch ex3 As Exception
                    'msg.log(ex3.Message)
                    Debug.WriteLine(ex.Message)
                    Return False
                End Try
                Return False
            Finally
                If cn.State = ConnectionState.Open Then
                    cn.Close()
                End If
                System.Threading.Monitor.Exit(lockObject)
            End Try


        End If
        Return True
    End Function

    Public Shared Function GetDbData(ByVal obj As Object) As Object
        If IsDBNull(obj) Then
            Return Nothing
        Else
            Return obj
        End If
    End Function

End Class

Public Class TranstrationItem
    Inherits Dictionary(Of String, System.Data.Common.DbParameter())
End Class


